package com.decent.ejfadmin.product.dao;

import com.decent.ejfadmin.common.bean.*;
import com.decent.ejfadmin.product.dao.provider.ProductDaoProvider;
import com.decent.ejfadmin.product.request.*;
import com.decent.ejfadmin.product.response.dto.ModelPriceDto;
import com.decent.ejfadmin.product.response.dto.UserPriceDto;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author zhangchao
 * @date 2018/7/25 11:09
 */
@Repository
@Mapper
public interface ProductDao {

    /**
     * 查询产品总数
     *
     * @param param
     * @return
     */
    @Select("<script>" +
            "select count(1) from yuhuang_supply_product " +
            "where 1= 1 " +
            " <if test=\"productName != null and productName != '' \"> AND  instr(product_name , #{productName})>0 </if>" +
            " <if test=\"productType != null\"> AND  product_type = #{productType}</if>" +
            " <if test=\"area != null and area != '' \"> AND  area = #{area}</if>" +
            " <if test=\"face != null\"> AND  face = #{face}</if>" +
            " <if test=\"type != null\"> AND  type = #{type}</if>" +
            " <if test=\"state != null\"> AND  state = #{state}</if>" +
            " <if test=\"modified != null\"> AND  modified = #{modified}</if>" +
            " <if test=\"admin != null\"> AND  admin = #{admin}</if>" +
            " <if test=\"ticketSpuid != null\"> AND  ticket_spuid = #{ticketSpuid}</if>" +
            "</script>")
    int queryProductCount(ProductQueryParam param);

    /**
     * 查询产品列表分页数据
     *
     * @param param
     * @return
     */
    @SelectProvider(type = ProductDaoProvider.class, method = "queryProductPage")
    List<SupplyProduct> queryProductPage(@Param("param") ProductQueryParam param);


    /**
     * 查询产品名称
     *
     * @param productIds
     * @return
     */
    @Select("SELECT product_name " +
            "FROM " +
            "yuhuang_supply_product where id in(${productIds}) and product_type>310")
    List<String> querySupplyProducts(@Param("productIds") String productIds);

    /**
     * 修改产品上下架
     *
     * @param productList
     * @param status
     * @param remark
     */
    @Update("<script>" +
            "update yuhuang_supply_product set state = #{status},remark=#{remark},modified = sysdate()  where 1= 1 " +
            "<if test=\"productList != null and productList.size() > 0 \" > " +
            "<foreach collection= \"productList\" item= \"item\" index= \"index\" open = \" and id in (\" close = \")\" separator= \",\" >" +
            " #{item} " +
            "</foreach>" +
            "</if> " +
            "</script>")
    void changeProductStatus(@Param("productList") List<Long> productList, @Param("status") Integer status, @Param("remark") String remark);

    /**
     * 查询产品id列表
     *
     * @param faceList
     * @param productTypeList
     * @param areaList
     * @return
     */
    @Select("<script>" +
            "select id from yuhuang_supply_product where 1=1 " +
            "<if test=\"faceList != null and faceList.size() > 0 \" > " +
            "<foreach collection= \"faceList\" item= \"item\" index= \"index\" open = \" and face in (\" close = \")\" separator= \",\" >" +
            " #{item} " +
            "</foreach>" +
            "</if> " +
            "<if test=\"productTypeList != null and productTypeList.size() > 0 \" > " +
            "<foreach collection= \"productTypeList\" item= \"item\" index= \"index\" open = \" and product_type in (\" close = \")\" separator= \",\" >" +
            " #{item} " +
            "</foreach>" +
            "</if> " +
            "<if test=\"areaList != null and areaList.size() > 0 \" > " +
            "<foreach collection= \"areaList\" item= \"item\" index= \"index\" open = \" and area in (\" close = \")\" separator= \",\" >" +
            " #{item} " +
            "</foreach>" +
            "</if> " +
            "</script>")
    List<Long> queryProductIdList(@Param("faceList") List<Integer> faceList, @Param("productTypeList") List<Integer> productTypeList, @Param("areaList") List<String> areaList);

    /**
     * 更新会员密价
     *
     * @param param
     * @return
     */
    @Update("<script>" +
            "update yuhuang_user_product_price set modify_time = sysdate()" +
            " <if test=\"minPrice != null\"> ,min_price = #{minPrice}</if>" +
            " <if test=\"maxPrice != null\"> ,max_price = #{maxPrice}</if>" +
            " <if test=\"xianyuPrice != null\"> ,xianyu_price = #{xianyuPrice}</if>" +
            " <if test=\"admin != null\"> ,admin = #{admin}</if>" +
            " <if test=\"state != null\"> ,state = #{state}</if>" +
            "where 1=1 " +
            "<if test=\"productIdList != null and productIdList.size() > 0 \" > " +
            "<foreach collection= \"productIdList\" item= \"item\" index= \"index\" open = \" and product_id in (\" close = \")\" separator= \",\" >" +
            " #{item} " +
            "</foreach>" +
            "</if> " +
            "<if test=\"userIdList != null and userIdList.size() > 0 \" > " +
            "<foreach collection= \"userIdList\" item= \"item\" index= \"index\" open = \" and user_id in (\" close = \")\" separator= \",\" >" +
            " #{item} " +
            "</foreach>" +
            "</if>" +
            "</script> ")
    void updateUserPrice(SetUpUserPriceParam param);

    /**
     * 设置会员密价
     *
     * @param userProductPriceList
     * @return
     */
    @Insert("<script>" +
            "insert into yuhuang_user_product_price (user_id,product_id,min_price,max_price,xianyu_price,modify_time,admin) " +
            "values " +
            "<foreach collection= \"list\" item= \"item\" index= \"index\" separator= \",\" >" +
            " (#{item.userId},#{item.productId},#{item.minPrice},#{item.maxPrice},#{item.xianyuPrice},sysdate(),#{item.admin} )" +
            "</foreach>" +
            " ON DUPLICATE KEY UPDATE min_price = values(min_price),max_price= values(max_price),xianyu_price=values(xianyu_price) " +
            " ,admin= values(admin)  " +
            "</script>")
    void addUserPrice(@Param("list") List<UserProductPrice> userProductPriceList);

    /**
     * 删除会员密价
     *
     * @param param
     * @return
     */
    @Delete("<script>" +
            "delete from yuhuang_user_product_price " +
            "where 1=1 " +
            "<if test=\"productIdList != null and productIdList.size() > 0 \" > " +
            "<foreach collection= \"productIdList\" item= \"item\" index= \"index\" open = \" and product_id in (\" close = \")\" separator= \",\" >" +
            " #{item} " +
            "</foreach>" +
            "</if> " +
            "<if test=\"userIdList != null and userIdList.size() > 0 \" > " +
            "<foreach collection= \"userIdList\" item= \"item\" index= \"index\" open = \" and user_id in (\" close = \")\" separator= \",\" >" +
            " #{item} " +
            "</foreach>" +
            "</if>" +
            "</script>")
    void deleteUserPrice(SetUpUserPriceParam param);


    /**
     * 更新会员模板密价
     *
     * @param param
     * @return
     */
    @Update("<script>" +
            "update yuhuang_supply_model_price_map set modified_time = sysdate()" +
            " <if test=\"minPrice != null\"> ,min_price = #{minPrice}</if>" +
            " <if test=\"maxPrice != null\"> ,max_price = #{maxPrice}</if>" +
            " <if test=\"admin != null\"> ,admin = #{admin}</if>" +
            " <if test=\"state != null\"> ,state = #{state}</if>" +
            "where 1=1 " +
            "<if test=\"productIdList != null and productIdList.size() > 0 \" > " +
            "<foreach collection= \"productIdList\" item= \"item\" index= \"index\" open = \" and product_id in (\" close = \")\" separator= \",\" >" +
            " #{item} " +
            "</foreach>" +
            "</if> " +
            " <if test=\"modelId != null\">  and model_id = #{modelId}</if>" +
            "</script> ")
    void updateModelPrice(SetUpModelPriceParam param);

    /**
     * 设置会员模板密价
     *
     * @param supplyModelPriceList
     * @return
     */
    @Insert("<script>" +
            "insert into yuhuang_supply_model_price_map (model_id,product_id,min_price,max_price,modified_time,admin) " +
            "values " +
            "<foreach collection= \"supplyModelPriceList\" item= \"item\" index= \"index\" separator= \",\" >" +
            " (#{item.modelId},#{item.productId},#{item.minPrice},#{item.maxPrice},sysdate(),#{item.admin} )" +
            "</foreach>" +
            " ON DUPLICATE KEY UPDATE min_price = values(min_price),max_price= values(max_price)\n" +
            ",admin= values(admin) " +
            "</script>")
    void addModelPrice(@Param("supplyModelPriceList") List<SupplyModelPrice> supplyModelPriceList);

    /**
     * 删除会员模板密价
     *
     * @param param
     * @return
     */
    @Delete("<script>" +
            "delete from yuhuang_supply_model_price_map " +
            "where 1=1 " +
            "<if test=\"productIdList != null and productIdList.size() > 0 \" > " +
            "<foreach collection= \"productIdList\" item= \"item\" index= \"index\" open = \" and product_id in (\" close = \")\" separator= \",\" >" +
            " #{item} " +
            "</foreach>" +
            "</if> " +
            " <if test=\"modelId != null\">  and model_id = #{modelId}</if>" +
            "</script>")
    void deleteModelPrice(SetUpModelPriceParam param);

    /**
     * 获取会员模板密价总数
     *
     * @param param
     * @return
     */
    @Select("<script>" +
            "select count(1) from yuhuang_supply_model ysm " +
            "inner join yuhuang_supply_model_price_map ysmpm on ysm.id = ysmpm.model_id " +
            "inner join yuhuang_supply_product ysp on ysmpm.product_id = ysp.id " +
            " <if test=\"supplyModel != null \"> AND  ysm.id = #{supplyModel}</if>" +
            " <if test=\"productName != null and productName != ''\"> AND  ysp.product_name = #{productName}</if>" +
            " <if test=\"productType != null\"> AND  ysp.product_type = #{productType}</if>" +
            " <if test=\"face != null\"> AND  ysp.face = #{face}</if>" +
            "</script>")
    int queryModelPriceCount(ModelPriceParam param);

    /**
     * 获取会员模板密价分页数据
     *
     * @param param
     * @return
     */
    @SelectProvider(type = ProductDaoProvider.class, method = "queryModelPricePage")
    List<ModelPriceDto> queryModelPricePage(@Param("param") ModelPriceParam param);

    /**
     * 获取会员密价总数
     *
     * @param param
     * @return
     */
    @Select("<script>" +
            "select count(1) from yuhuang_user_product_price yupp " +
            "inner join yuhuang_supply_product ysp on yupp.product_id = ysp.id " +
            "inner join yuhuang_sup_user ysu on yupp.user_id = ysu.user_id " +
            "inner join yuhuang_sup_info ysi on yupp.user_id = ysi.user_id where 1=1 " +
            " <if test=\"productName != null and productName != ''\"> AND  ysp.product_name = #{productName}</if>" +
            " <if test=\"productType != null\"> AND  ysp.product_type = #{productType}</if>" +
            " <if test=\"face != null\"> AND  ysp.face = #{face}</if>" +
            " <if test=\"userAccount != null and userAccount != ''\"> AND  (instr(ysu.account, #{userAccount})>0 or instr(ysi.name, #{userAccount})>0)</if>" +
            " <if test=\"state != null\"> and yupp.state=#{state}</if>" +
            " <if test=\"minDiscount != null\"> AND  yupp.max_price >= #{minDiscount} </if>" +
            " <if test=\"maxDiscount != null\"> AND yupp.max_price &lt;= #{maxDiscount} </if>" +
            "</script>")
    int queryUserPriceCount(UserPriceParam param);

    /**
     * 获取会员密价分页数据
     *
     * @param param
     * @return
     */
    @SelectProvider(type = ProductDaoProvider.class, method = "queryUserPricePage")
    List<UserPriceDto> queryUserPricePage(@Param("param") UserPriceParam param);

    /**
     * 添加密价模板
     *
     * @param supplyModel
     * @return
     */
    @Insert("insert into yuhuang_supply_model(model_name,admin,remark) values(#{modelName},#{admin},#{remark})")
    void insert(SupplyModel supplyModel);

    /**
     * 修改密价模板
     *
     * @param supplyModel
     * @return
     */
    @Update("<script>" +
            "update yuhuang_supply_model set id = id " +
            " <if test=\"modelName != null and modelName != ''\"> ,model_name = #{modelName}</if>" +
            " <if test=\"admin != null\"> ,admin = #{admin}</if>" +
            " <if test=\"remark != null and remark != ''\"> ,remark = #{remark}</if>" +
            " where id = #{id}" +
            "</script>")
    void update(SupplyModel supplyModel);

    /**
     * 批量插入返点
     *
     * @param supRebateConfig
     */
    @Insert("<script>" +
            "insert into yuhuang_sup_rebate_config (userId,discount) " +
            "values " +
            " (#{supRebateConfig.userId},#{supRebateConfig.discount} )" +
            " ON DUPLICATE KEY UPDATE discount= #{supRebateConfig.discount}" +
            "</script>")
    void insertRebateConfig(@Param("supRebateConfig") SupRebateConfig supRebateConfig);

    /**
     * 获取所有会员模板密价
     *
     * @param param 是否查询超快销和慢销
     * @return
     */
    @SelectProvider(type = ProductDaoProvider.class, method = "queryAllModel")
    List<ModelPriceDto> queryAllModel(@Param("param") boolean param);

    /**
     * 根据id获取会员密价模板
     *
     * @param id 模板id
     * @return
     */
    @Select("select id ,model_name modelName,remark from yuhuang_supply_model where id=#{id}")
    ModelPriceDto queryModelById(@Param("id") Long id);

    /**
     * 快速修改产品状态
     *
     * @param param
     * @return
     */
    @Update("<script>" +
            "update yuhuang_supply_product set modified = sysdate()" +
            " <if test=\"admin != null\"> ,admin = #{admin}</if>" +
            " <if test=\"state != null\"> ,state = #{state}</if>" +
            "where 1=1 " +
            "<if test=\"productIdList != null and productIdList.size() > 0 \" > " +
            "<foreach collection= \"productIdList\" item= \"item\" index= \"index\" open = \" and id in (\" close = \")\" separator= \",\" >" +
            " #{item} " +
            "</foreach>" +
            "</if> " +
            " <if test=\"modelId != null\">  and model_id = #{modelId}</if>" +
            "</script> ")
    void updateProductStatus(SetUpModelPriceParam param);

    /**
     * 查询所有会员
     *
     * @param userAccount 会员编号
     * @return
     */
    @Select("select u.user_id userId,i.name  'supInfo.name' from yuhuang_sup_user u,yuhuang_sup_info i where u.user_id=i.user_id and u.account in(${userAccount})")
    List<SupUser> queryUserByAccount(@Param("userAccount") String userAccount);

    /**
     * 通过运营商查询对应的产品类型
     *
     * @param productType
     * @return
     */
    @Select(" select p.card_type_id from yuhuang_card_operator p where p.card_operator_id = #{productType} ")
    Integer queryTypeByProductType(@Param("productType") Integer productType);

    /**
     * 新增产品
     *
     * @param product
     * @return
     */
    @Insert(" INSERT INTO yuhuang_supply_product\n" +
            "  (product_name,\n" +
            "   product_type,\n" +
            "   area,\n" +
            "   face,\n" +
            "   type,\n" +
            "   state,\n" +
            "   modified,\n" +
            "   admin,\n" +
            "   card_product_id,\n" +
            "   submit_type,\n" +
            "   card_restrict)\n" +
            "VALUES\n" +
            "  (#{product.productName},\n" +
            "   #{product.productType},\n" +
            "   #{product.area},\n" +
            "   #{product.face},\n" +
            "   #{product.type},\n" +
            "   #{product.state},\n" +
            "   NOW(),\n" +
            "   #{product.admin},\n" +
            "   #{product.cardProductId},\n" +
            "   #{product.submitType},\n" +
            "   #{product.cardRestrict})\n ")
    int addProduct(@Param("product") SupplyProduct product);

    /**
     * 修改产品列表
     *
     * @param product
     * @return
     */
    @Update("update yuhuang_supply_product" +
            " set product_name=#{product.productName}," +
            " product_type=#{product.productType}," +
            " area=#{product.area}," +
            " type=#{product.type}," +
            " state=#{product.state}," +
            " modified=now()," +
            " admin=#{product.admin}," +
            " card_product_id=#{product.cardProductId}," +
            " submit_type=#{product.submitType}," +
            " card_restrict=#{product.cardRestrict}," +
            " face=#{product.face} " +
            " where id=#{product.id}")
    int editProduct(@Param("product") SupplyProduct product);

    /**
     * 编辑产品校验规则
     *
     * @param productIds   产品id
     * @param cardRestrict 校验规则
     * @param name         操作者
     * @return
     */
    @UpdateProvider(type = ProductDaoProvider.class, method = "updateCardRestrict")
    int updateCardRestrict(@Param("productIds") List<Long> productIds, @Param("cardRestrict") String cardRestrict, @Param("name") String name);

    /**
     * 删除产品
     *
     * @param productIds 产品id
     * @return
     */
    @Delete("delete from yuhuang_supply_product where id in (${productIds})")
    int deleteProduct(@Param("productIds") String productIds);

    /**
     * 查询会员
     *
     * @param userAccount
     * @return
     */
    @Select("select u.user_id userId from yuhuang_sup_user u,yuhuang_sup_info i where u.user_id=i.user_id and u.account =#{userAccount} ")
    SupUser queryUserAccount(@Param("userAccount") String userAccount);

    /**
     * 清除元目标产品密价
     *
     * @param targetId
     * @param productType
     * @return
     */
    @SelectProvider(type = ProductDaoProvider.class, method = "getClearProductId")
    List<Long> getClearProductId(@Param("targetId") Long targetId, @Param("productType") Integer productType);

    /**
     * 删除某类产品
     *
     * @param targetId
     * @param productIds
     */
    @Delete("delete from yuhuang_user_product_price where user_id=#{targetId} and product_id in (${productIds})")
    void clearPriceByUserId(@Param("targetId") Long targetId, @Param("productIds") String productIds);

    /**
     * 克隆密价
     *
     * @param forId
     * @param targetId
     * @param productId
     * @param admin
     * @return
     */
    @Insert("insert into yuhuang_user_product_price " +
            "(user_id,product_id,min_price,max_price,modify_time,admin,remark)" +
            "   SELECT #{targetId},\n" +
            "            product_id," +
            "            min_price,\n" +
            "            max_price,\n" +
            "            now(),\n" +
            "            #{admin}," +
            "            '克隆密价' \n" +
            "     FROM   yuhuang_user_product_price \n" +
            "    WHERE   user_id = #{forId} and product_id=#{productId}")
    int cloneUserPrice(@Param("forId") Long forId, @Param("targetId") Long targetId, @Param("productId") Long productId, @Param("admin") String admin);

    /**
     * 根据产品编号查询产品信息
     *
     * @param productNo
     * @return
     */
    @Select("select * from yuhuang_supply_product where card_product_id=#{productNo} ")
    SupplyProduct queryProductByProductNo(@Param("productNo") String productNo);

    /**
     * 更新会员密价状态
     *
     * @param ids
     * @param status
     * @param adminName
     * @return
     */
    @Update("update yuhuang_user_product_price set state=#{status},modify_time=now(),remark=#{adminName} where id in (${ids})")
    int updateCecretPriceStatus(@Param("ids") String ids, @Param("status") Integer status, @Param("adminName") String adminName);

    /**
     * 查询spu对应卡种是否存在
     *
     * @param spuid
     * @param productType
     * @return
     */
    @Select("select * from yuhuang_supply_product where ticket_spuid=#{spuid} and product_type=#{productType} limit 1")
    SupplyProduct querySpuBySupplyProduct(@Param("spuid") Integer spuid, @Param("productType") Integer productType);

}
